﻿using System;
using System.Data;
using System.Data.Common;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;


namespace ZhangBY.Data.SqlServer
{
    public  partial class DataProvider : IDataProvider
    {
        /// <summary>
        /// 取得最后一个企业ID
        /// </summary>
        /// <param name="diynum">是否自选号码</param>
        /// <returns></returns>
        public IDataReader GetCompanyLastId(bool diynum)
        {
            DbParameter parms = DbHelper.MakeInParam("@diynum", (DbType)SqlDbType.Bit, 1, diynum);
            string commandText = "SELECT [companynum],[diynum] FROM [dbo].[zby_companyuser] AS a WHERE NOT EXISTS(SELECT 1 FROM [dbo].[zby_companyuser]WHERE [diynum]=@diynum AND [companynum]> [a].[companynum])";					  
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 添加企业用户
        /// </summary>
        /// <param name="comUserInfo"></param>
        /// <returns></returns>
        public int InsertCompanyUser(CompanyUserInfo comUserInfo)
        {
            DbParameter[] parms ={
                                    DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,comUserInfo.UserId),
                                    DbHelper.MakeInParam("@companynum",(DbType)SqlDbType.NChar,20,comUserInfo.UserUid),
                                    DbHelper.MakeInParam("@vip",(DbType)SqlDbType.TinyInt,Int16.MaxValue,comUserInfo.Vip),
                                    DbHelper.MakeInParam("@companyname",(DbType)SqlDbType.NVarChar,40,comUserInfo.CompanyName),
                                    DbHelper.MakeInParam("@area",(DbType)SqlDbType.NVarChar,50,comUserInfo.Area),
                                    DbHelper.MakeInParam("@address",(DbType)SqlDbType.NVarChar,50,comUserInfo.Address),
                                    DbHelper.MakeInParam("@phone",(DbType)SqlDbType.NVarChar,50,comUserInfo.Phone),
                                    DbHelper.MakeInParam("@mobile",(DbType)SqlDbType.NVarChar,50,comUserInfo.Mobile),
                                    DbHelper.MakeInParam("@fax",(DbType)SqlDbType.NVarChar,50,comUserInfo.Fax),
                                    DbHelper.MakeInParam("@email",(DbType)SqlDbType.NChar,40,comUserInfo.Email),
                                    DbHelper.MakeInParam("@qq",(DbType)SqlDbType.NChar,44,comUserInfo.QQ),
                                    DbHelper.MakeInParam("@msn",(DbType)SqlDbType.NChar,40,comUserInfo.Msn),
                                    DbHelper.MakeInParam("@diynum",(DbType)SqlDbType.Bit,6,comUserInfo.DiyNum),
                                    DbHelper.MakeInParam("@key1",(DbType)SqlDbType.NVarChar,100,comUserInfo.Key1),
                                    DbHelper.MakeInParam("@key2",(DbType)SqlDbType.NVarChar,100,comUserInfo.Key2),
                                    DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,250,comUserInfo.Description),
                                    DbHelper.MakeInParam("@index",(DbType)SqlDbType.NVarChar,50,comUserInfo.Index),
                                    DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,6,comUserInfo.Audit),
                                    DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,comUserInfo.Date.ToString().Length+2,comUserInfo.Date)
                                };
            return DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "[dbo].[sp_insertCompanyUser]",parms);
        }
        /// <summary>
        /// 根据用户ID查找企业用户信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataSet GetCompanyUserInfo(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar,60,userid);
            string commandText = "SELECT * FROM [dbo].[zby_companyuser] WHERE [userguid]=@userid ";
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 更新企业用户信息
        /// </summary>
        /// <param name="comUserInfo"></param>
        /// <returns></returns>
        public int UpDateCompanyUser(CompanyUserInfo comUserInfo)
        {
            DbParameter[] parms ={
                                    DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,comUserInfo.UserId),
                                    DbHelper.MakeInParam("@companyname",(DbType)SqlDbType.NVarChar,40,comUserInfo.CompanyName),
                                    DbHelper.MakeInParam("@area",(DbType)SqlDbType.NVarChar,50,comUserInfo.Area),
                                    DbHelper.MakeInParam("@address",(DbType)SqlDbType.NVarChar,50,comUserInfo.Address),
                                    DbHelper.MakeInParam("@phone",(DbType)SqlDbType.NVarChar,50,comUserInfo.Phone),
                                    DbHelper.MakeInParam("@mobile",(DbType)SqlDbType.NVarChar,50,comUserInfo.Mobile),
                                    DbHelper.MakeInParam("@fax",(DbType)SqlDbType.NVarChar,50,comUserInfo.Fax),
                                    DbHelper.MakeInParam("@email",(DbType)SqlDbType.NChar,40,comUserInfo.Email),
                                    DbHelper.MakeInParam("@qq",(DbType)SqlDbType.NChar,44,comUserInfo.QQ),
                                    DbHelper.MakeInParam("@msn",(DbType)SqlDbType.NChar,40,comUserInfo.Msn),
                                    DbHelper.MakeInParam("@key1",(DbType)SqlDbType.NVarChar,100,comUserInfo.Key1),
                                    DbHelper.MakeInParam("@key2",(DbType)SqlDbType.NVarChar,100,comUserInfo.Key2),
                                    DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,250,comUserInfo.Description),
                                    DbHelper.MakeInParam("@index",(DbType)SqlDbType.NVarChar,50,comUserInfo.Index)
                                };
            return DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "[dbo].[sp_updateCompanyUser]", parms);
        }

        #region 企业相册
        /// <summary>
        /// 增加图片信息
        /// </summary>
        /// <param name="imgFileInfo"></param>
        /// <returns></returns>
        public int InsertImgFile(ImgFileInfo imgFileInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@id",(DbType)SqlDbType.NVarChar,60,imgFileInfo.FileId),
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,imgFileInfo.UserId),
                                      DbHelper.MakeInParam("@filename",(DbType)SqlDbType.NChar,30,imgFileInfo.FileName.ToLower()),
                                      DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,100,imgFileInfo.Description),
                                      DbHelper.MakeInParam("@size",(DbType)SqlDbType.NChar,10,imgFileInfo.FileSize),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,imgFileInfo.FileDate.Length+2,imgFileInfo.FileDate)
                                  };
            string commandText="INSERT INTO[dbo].[zby_imgfile]([id],[userguid],[filename],[description],[size],[date])VALUES(@id,@userid,@filename,@description,@size,@date)";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 返回指定用户的所有图片
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetImgFiles(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = "SELECT * FROM [dbo].[zby_imgfile] WHERE [userguid]=@userid ORDER BY [date] DESC";
            return DbHelper.ExecuteReader(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据指定的用户ID统计图片记录
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <returns></returns>
        public object GetCountImgFiles(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid);
            string commandText = "SELECT COUNT(id) AS counts FROM [dbo].[zby_imgfile] WHERE [userguid]=@userid";
            return DbHelper.ExecuteScalar(CommandType.Text,commandText,parms);

        }
        /// <summary>
        /// 统计图片记录总数
        /// </summary>
        /// <returns></returns>
        public object  GetCountImgFiles()
        {
            string commandText = "SELECT COUNT(id) AS counts FROM [dbo].[zby_imgfile]";
            return DbHelper.ExecuteScalar(CommandType.Text, commandText);
        }
        /// <summary>
        /// 根据指定的ID删除图片
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelImgFiles(string id)
        {
            DbParameter parms = DbHelper.MakeInParam("@id", (DbType)SqlDbType.NVarChar, 60, id);
            string commandText = "DELETE FROM [dbo].[zby_imgfile] WHERE [id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据ID更新图片数据
        /// </summary>
        /// <param name="imgFileInfo"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpDateImgFilesById(ImgFileInfo imgFileInfo,string id)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@id",(DbType)SqlDbType.NVarChar,60,id),
                                      DbHelper.MakeInParam("@filename",(DbType)SqlDbType.NChar,30,imgFileInfo.FileName.ToLower()),
                                      DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,100,imgFileInfo.Description),
                                      DbHelper.MakeInParam("@size",(DbType)SqlDbType.NChar,10,imgFileInfo.FileSize),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,imgFileInfo.FileDate.Length+2,imgFileInfo.FileDate)
                                  };
            string commandText = "UPDATE [dbo].[zby_imgfile] SET [filename]=@filename,[description]=@description,[size]=@size,[date]=@date WHERE [id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        #endregion

        #region 企业LOGO
        /// <summary>
        /// 增加一条企业LOGO文件信息
        /// </summary>
        /// <param name="imgFileInfo"></param>
        /// <returns></returns>
        public int InsertComLogo(ImgFileInfo imgFileInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@logoid",(DbType)SqlDbType.NVarChar,60,imgFileInfo.FileId),
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,imgFileInfo.UserId),
                                      DbHelper.MakeInParam("@logoname",(DbType)SqlDbType.NChar,30,imgFileInfo.FileName),
                                      DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,50,imgFileInfo.Description),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,imgFileInfo.FileDate.Length,imgFileInfo.FileDate)                                  
                                  };
            string commandText = "INSERT INTO[dbo].[zby_comlogo]([logoid],[userguid],[logoname],[description],[date])VALUES(@logoid,@userid,@logoname,@description,@date)";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 更新企业LOGO文件信息
        /// </summary>
        /// <param name="imgFileInfo"></param>
        /// <returns></returns>
        public int UpdateComLogoByLogoId(ImgFileInfo imgFileInfo, string logoId)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@logoid",(DbType)SqlDbType.NVarChar,60,logoId),
                                      DbHelper.MakeInParam("@logoname",(DbType)SqlDbType.NChar,30,imgFileInfo.FileName),
                                      DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,50,imgFileInfo.Description),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,imgFileInfo.FileDate.Length,imgFileInfo.FileDate)    
                                  };
            string commandText = "UPDATE [dbo].[zby_comlogo] SET [logoname]=@logoname,[description]=@description,[date]=@date WHERE [logoid]=@logoid";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据指定LOGOID查找LOGO文件信息
        /// </summary>
        /// <param name="logoId"></param>
        /// <returns></returns>
        public DataSet GetComLogoInfoByLogoId(string logoId)
        {
            DbParameter parms = DbHelper.MakeInParam("@logoid", (DbType)SqlDbType.NVarChar, 60, logoId);
            string commandText = "SELECT * FROM [dbo].[zby_comlogo] WHERE [logoid]=@logoid";
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据指定userid查找logo文件信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataSet GetComLogoInfoByUserId(string userId)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userId);
            string commandText = "SELECT * FROM [dbo].[zby_comlogo] WHERE [userguid]=@userid";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms);
        }
        #endregion

        #region 企业招牌
        /// <summary>
        /// 增加一条企业招牌文件信息
        /// </summary>
        /// <param name="imgFileInfo"></param>
        /// <returns></returns>
        public int InsertComBanner(ImgFileInfo imgFileInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@bannerid",(DbType)SqlDbType.NVarChar,60,imgFileInfo.FileId),
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,imgFileInfo.UserId),
                                      DbHelper.MakeInParam("@bannername",(DbType)SqlDbType.NChar,30,imgFileInfo.FileName),
                                      DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,50,imgFileInfo.Description),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,imgFileInfo.FileDate.Length,imgFileInfo.FileDate)                                  
                                  };
            string commandText = "INSERT INTO[dbo].[zby_combanner]([userguid],[bannerid],[bannername],[description],[date])VALUES(@userid,@bannerid,@bannername,@description,@date)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 更新企业招牌文件信息
        /// </summary>
        /// <param name="imgFileInfo"></param>
        /// <returns></returns>
        public int UpdateComBannerByBannerId(ImgFileInfo imgFileInfo, string bannerId)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@bannerid",(DbType)SqlDbType.NVarChar,60,bannerId),
                                      DbHelper.MakeInParam("@bannername",(DbType)SqlDbType.NChar,30,imgFileInfo.FileName),
                                      DbHelper.MakeInParam("@description",(DbType)SqlDbType.NVarChar,50,imgFileInfo.Description),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,imgFileInfo.FileDate.Length,imgFileInfo.FileDate)    
                                  };
            string commandText = "UPDATE [dbo].[zby_combanner] SET [bannername]=@bannername,[description]=@description,[date]=@date WHERE [bannerid]=@bannerid";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据指定bannerID查找招牌文件信息
        /// </summary>
        /// <param name="bannerId"></param>
        /// <returns></returns>
        public DataSet GetComBannerInfoByBannerId(string bannerId)
        {
            DbParameter parms = DbHelper.MakeInParam("@bannerid", (DbType)SqlDbType.NVarChar, 60,bannerId);
            string commandText = "SELECT * FROM [dbo].[zby_combanner] WHERE [bannerid]=@bannerid";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据指定userid查找招牌文件信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataSet GetComBannerInfoByUserId(string userId)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userId);
            string commandText = "SELECT * FROM [dbo].[zby_combanner] WHERE [userguid]=@userid";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms);
        }
        #endregion
    }
}
